#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
# Copyright (c) 2010 by Derek Anderson
#

import collections, datetime, json, numpy, os, sqlite3, sys, tempfile, textwrap, threading, time, traceback, weakref, uuid

import sqlparse


try:
  import pygtk
  pygtk.require("2.0")
  import gtk
  import gtk.glade
  import gtksourceview2
  import gobject, gnome
  import pango
except:
  traceback.print_exc()
  sys.exit(1)

try:
  import sexy
  USE_LIBSEXY=True
except:
  USE_LIBSEXY=False


import gui_results
import metadata
from util import *


class Conn(object):

  def __init__(self,main,notebook,conn,db_type,conn_id,schema):
    self.main = main
    self.notebook = notebook
    self._conn = conn
    self.conn_lock = threading.RLock()
    self.db_type = db_type
    self.schema = schema
    self.conn_id = conn_id
    self.tran_id = None
    self.tran_start = None
    self.session_id = None
    self.sql_log_cache_ids = {}
    self.metadata = None
    self.last_conn_access_time = time.time()
    self.results = gui_results.Results(main, self)
    
    if self.db_type=='SQL Server': self.schema_tbl_sep = '..'
    else: self.schema_tbl_sep = '.'
    if self.db_type=='MySQL': self.paramstyle = '%s'
    elif self.db_type=='SQL Server': self.paramstyle = ':1'
    else: self.paramstyle = '?'
    
    self.top = gtk.VBox()
    toolbar = gtk.Toolbar()
    toolbar.set_icon_size(gtk.ICON_SIZE_MENU)
    self.top.pack_start(toolbar, expand=False)

    execute = gtk.ToolButton(gtk.STOCK_MEDIA_PLAY)
    execute.set_label('Execute')
    execute.set_tooltip_text('Execute the current SQL statement')
    execute.set_is_important(True)
    execute.set_sensitive(False)
    execute.connect('clicked',lambda x: self.execute_entry())
    toolbar.insert( execute, -1 )
    cancel = gtk.ToolButton(gtk.STOCK_CANCEL)
    cancel.set_sensitive(False)
    toolbar.insert( cancel, -1 )
    common = gtk.MenuToolButton(gtk.STOCK_HARDDISK)
    common.set_sensitive(True)
    common.set_tooltip_text('Most commonly executed SQL statements...')
    common_menu = gtk.Menu()
    common_queries = get_common_queries(self.conn_id)
    if common_queries:
      common.connect('clicked', lambda x: (self.query.get_buffer().set_text(common_queries[0]), self.format()))
    for sql in common_queries:
      sql = '\n'.join(textwrap.wrap(sql))
      item = gtk.MenuItem(pango_escape(sql))
      item.connect('activate', lambda x,s:(self.query.get_buffer().set_text(s), self.format()), sql )
      common_menu.append(item)
    common_menu.show_all()
    common.set_menu(common_menu)
    toolbar.insert( common, -1 )
    toolbar.insert( gtk.SeparatorToolItem(), -1 )
    self.rollback_button = gtk.ToolButton(gtk.STOCK_REVERT_TO_SAVED)
    self.rollback_button.set_tooltip_text('Rollback this transaction')
    self.rollback_button.set_label('Rollback')
    self.rollback_button.set_is_important(True)
    self.rollback_button.set_sensitive(False)
    self.rollback_button.connect('clicked',lambda x: self.rollback())
    toolbar.insert( self.rollback_button, -1 )
    self.commit_button = gtk.ToolButton(gtk.STOCK_APPLY)
    self.commit_button.set_label('Commit')
    self.commit_button.set_tooltip_text('Commit this transaction')
    self.commit_button.set_is_important(True)
    self.commit_button.set_sensitive(False)
    self.commit_button.connect('clicked',lambda x: self.commit())
    toolbar.insert( self.commit_button, -1 )
    toolbar.insert( gtk.SeparatorToolItem(), -1 )
    #toolbar.insert( gtk.ToolButton(gtk.STOCK_FULLSCREEN), -1 )
    refresh = gtk.ToolButton(gtk.STOCK_REFRESH)
    refresh.set_tooltip_text('Refresh the metadata for this connection')
    refresh.connect('clicked',lambda *_: self.load_metadata(use_cache=False))
    toolbar.insert( refresh, -1 )
    close = gtk.ToolButton(gtk.STOCK_CLOSE)
    close.set_tooltip_text('Close this connection')
    close.connect('clicked',lambda x: self.close())
    toolbar.insert( close, -1 )

    # setup metadata treeview
    self.metadata_treestore = gtk.TreeStore(str,str,str) # name, type, pk
    self.metadata_treefilter = self.metadata_treestore.filter_new()
    self.metadata_treefilter.set_visible_func(self.metadata_treefilter_callback)
    self.metadata_treeview = gtk.TreeView(self.metadata_treefilter)
    self.metadata_treeview.set_search_column(0)
    #self.metadata_treeview.set_headers_visible(False)
    column = gtk.TreeViewColumn('entity')
    column.set_resizable(True)
    self.metadata_treeview.append_column(column)
    cell = gtk.CellRendererText()
    column.pack_start(cell, True)
    column.add_attribute(cell, 'text', 0)
    column.set_sort_column_id(0)
    column = gtk.TreeViewColumn(PK_KEY)
    column.set_resizable(False)
    self.metadata_treeview.append_column(column)
    cell = gtk.CellRendererText()
    column.pack_start(cell, False)
    column.add_attribute(cell, 'text', 2)
    column.set_sort_column_id(0)
    column = gtk.TreeViewColumn('type')
    column.set_resizable(True)
    self.metadata_treeview.append_column(column)
    cell = gtk.CellRendererText()
    column.pack_start(cell, False)
    column.add_attribute(cell, 'text', 1)
    column.set_sort_column_id(0)
    metadata_treeview_scrolled_window = gtk.ScrolledWindow()
    metadata_treeview_scrolled_window.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
    metadata_treeview_scrolled_window.add(self.metadata_treeview)
    # setup metadata search
    metadata_vbox = gtk.VBox()
    metadata_entry_hbox = gtk.HBox()
    def metadata_entry_activate():
      self.metadata_treefilter.refilter()
      scroll_to_selection(self.metadata_treeview)
    if USE_LIBSEXY:
      self.metadata_entry = sexy.IconEntry()
      self.metadata_entry.add_clear_button()
      self.metadata_entry.connect('icon-released',lambda x,y,z:metadata_entry_activate())
    else:
      self.metadata_entry = gtk.Entry()
      self.metadata_entry.set_icon_from_stock(gtk.ENTRY_ICON_SECONDARY, gtk.STOCK_CLEAR)
      self.metadata_entry.connect('icon-press', lambda x,y,z: (self.metadata_entry.set_text(''), metadata_entry_activate()))
    self.metadata_entry.connect('activate',lambda x:metadata_entry_activate())
    metadata_entry_hbox.pack_start(gtk.Label('Search:'),expand=False)
    metadata_entry_hbox.pack_start(self.metadata_entry)
    metadata_vbox.pack_start(metadata_entry_hbox, expand=False)
    metadata_vbox.pack_start(metadata_treeview_scrolled_window)
    self.metadata_treeview.connect('row-activated',self.metadata_treeview_row_activated)
    self.metadata_treeview.connect('button_press_event',self.metadata_treeview_button_press_event)
    self.metadata_treeview.set_property('has-tooltip',True)
    self.metadata_treeview.connect('query-tooltip',self.metadata_treeview_query_tooltip_event)

    # setup log treeview
    self.log_treestore = gtk.TreeStore(str,str,bool,str,str,object,pango.Style) # class,repr,hidden,uuid,foreground_color,time,font_style
    self.log_treefilter = self.log_treestore.filter_new()
    self.log_treefilter.set_visible_func(self.log_treefilter_callback)
    self.log_treeview = gtk.TreeView(self.log_treefilter)
    self.log_treeview.set_headers_visible(False)
    self.log_treeview.get_selection().set_mode(gtk.SELECTION_MULTIPLE)
    column = gtk.TreeViewColumn('Object')
    self.log_treeview.append_column(column)
    cell = gtk.CellRendererText()
    cell.set_property('font','monospace')
    column.pack_start(cell, True)
    column.add_attribute(cell, 'foreground', 4)
    column.add_attribute(cell, 'text', 1)
    column.add_attribute(cell, 'style', 6)
    self.log_treeview.connect('row-activated',self.log_treeview_row_activated)
    self.log_treeview.connect('button_press_event',self.log_treeview_button_press_event)
    self.log_treeview.set_property('has-tooltip',True)
    self.log_treeview.connect('query-tooltip',self.log_treeview_query_tooltip_event)
    #self.log_treeview.connect('motion-notify-event',self.log_treeview_motion_notify_event)
    self.log_show_hidden_statements = False

    vpane = gtk.VPaned()
    vpane.connect('notify::position',self.handle_paned_notify_event,'results_pane_position')
    vpane.set_position( get_metadata(self.conn_id,'results_pane_position',640) )
    hpane = gtk.HPaned()
    hpane.connect('notify::position',self.handle_paned_notify_event,'metadata_pane_position')
    hpane.set_position( get_metadata(self.conn_id,'metadata_pane_position',320) )
    vpane.pack1(hpane, resize=True, shrink=False)
    #vpane.pack2(self.results.execute_notebook, resize=True, shrink=False)
    vpane.pack2(self.results.execute_vbox, resize=True, shrink=False)
    self.results.update_notebook_visible()
    scrolled_window = gtk.ScrolledWindow()
    scrolled_window.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
    scrolled_window.add(self.log_treeview)
    vpane2 = gtk.VPaned()
    vpane2.connect('notify::position',self.handle_paned_notify_event,'sql_log_pane_position')
    vpane2.set_position( get_metadata(self.conn_id,'sql_log_pane_position',640) )
    
    # setup log search
    log_vbox = gtk.VBox()
    log_entry_hbox = gtk.HBox()
    if USE_LIBSEXY:
      self.log_entry = sexy.IconEntry()
      self.log_entry.add_clear_button()
      self.log_entry.connect('icon-released',lambda x,y,z:self.log_treefilter.refilter())
    else:
      self.log_entry = gtk.Entry()
      self.log_entry.set_icon_from_stock(gtk.ENTRY_ICON_SECONDARY, gtk.STOCK_CLEAR)
      self.log_entry.connect('icon-press',lambda entry,y,z: (entry.set_text(''), self.log_treefilter.refilter()))
    self.log_entry.connect('activate',lambda x:self.log_treefilter.refilter())
    log_entry_hbox.pack_start(gtk.Label('Search:'),expand=False)
    log_entry_hbox.pack_start(self.log_entry)
    log_vbox.pack_start(log_entry_hbox, expand=False)
    log_vbox.pack_start(scrolled_window)

    vpane2.pack1(log_vbox, resize=True, shrink=False)
    self.query = gtksourceview2.View()
    self.query.set_buffer( gtksourceview2.Buffer() )
    self.query.get_buffer().set_highlight_syntax(True)
    self.query.get_buffer().set_language( gtksourceview2.language_manager_get_default().get_language('sql') )
    self.query.modify_font(pango.FontDescription('monospace 8'))
    self.query.get_buffer().connect('changed', lambda x:execute.set_sensitive(x.get_char_count()) )
    scrolled_window = gtk.ScrolledWindow()
    scrolled_window.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
    scrolled_window.add(self.query)
    vpane2.pack2(scrolled_window, resize=True, shrink=False)
    hpane.pack1(vpane2, resize=True, shrink=False)
    hpane.pack2(metadata_vbox, resize=True, shrink=False)
    self.top.pack_start(vpane)
    
    self.load_metadata()
    self.load_sql_log()
    self.start_session()
    self.log_treeview.expand_all()
    self.start_conn_watcher()


  def handle_paned_notify_event(self,pane,signal,prop_name):
    x = pane.get_position()
    set_metadata(self.conn_id,prop_name,x)
    
    
  def show_all(self):
    self.node().show_all()
    self.results.update_notebook_visible()


  def get_conn(self):
    self.last_conn_access_time = time.time()
    if self._conn and self.db_type=='SQLite3':
      # this fails if we're in the wrong thread - sqlite3 won't let you access a conn from two different threads
      try: self._conn.cursor().close()
      except: self._conn = None
    if not self._conn:
      conn_id, conn = create_conn(conn_id=self.conn_id)
      self._conn = conn
      label = self.notebook.get_tab_label(self.node())
      if label:
        while len(label.get_children())>1:
          label.remove(label.get_children()[-1])
        img = gtk.image_new_from_stock(gtk.STOCK_CONNECT, gtk.ICON_SIZE_MENU)
        img.set_tooltip_text('Connection to database is active')
        img.show()
        label.add(img)
    return self._conn


  def start_conn_watcher(self):
    def run():
      while True:
        time.sleep(60)
        with self.conn_lock:
          if self._conn and self.conn_id and not self.tran_id and time.time() - self.last_conn_access_time > 10*60:
            self._conn.close()
            self._conn = None
            print 'closing connection', self.conn_id
            label = self.notebook.get_tab_label(self.node())
            with gtk.gdk.lock:
              while len(label.get_children())>1:
                label.remove(label.get_children()[-1])
              img = gtk.image_new_from_stock(gtk.STOCK_DISCONNECT, gtk.ICON_SIZE_MENU)
              img.set_tooltip_text('Connection to database will be restablished on demand')
              img.show()
              label.add(img)
#            self.notebook.get_tab_label(self.node()).get_children()[-1].set_sensitive(False)
          if self.tran_id and self.tran_start and time.time() - self.tran_start > 4*60*60:
            print 'warning tran is old!', self.tran_id, self.tran_start, (time.time() - self.tran_start)/60/60, 'hours'
            with gtk.gdk.lock:
              dialog = gtk.MessageDialog(parent=None, flags=0, type=gtk.MESSAGE_WARNING, 
                                         buttons=gtk.BUTTONS_YES_NO, message_format=None)
              dialog.set_markup('<b>An old transaction is still open!</b>\n\nTransaction <i>%s</i> has now been open for over %i hours.  Would you like to issue a rollback now?' % (self.tran_id,round((time.time() - self.tran_start)/60/60)))
              if dialog.run()==-8:
                #self.rollback()
                self._conn.rollback()
                self.reset_trans_stuff()
              dialog.destroy()

    t = threading.Thread(target=run)
    t.daemon = True
    t.start()


  def log_treeview_query_tooltip_event(self, widget, x, y, keyboard_mode, tooltip):
    #print 'widget, x, y, keyboard_mode, tooltip', widget, x, y, keyboard_mode, tooltip
    pos = self.log_treeview.get_path_at_pos(x,y)
    if pos:
      path, col, x, y = pos
      if path:
        t = self.log_treeview.get_model()[path][5]
        kind = self.log_treeview.get_model()[path][0]
        s = time.strftime("%A, %B %d %Y %I:%M%p",time.localtime(t))
        tooltip.set_text('%s started on %s' % (kind,s))
        return True


  def metadata_treeview_query_tooltip_event(self, treeview, x, y, keyboard_mode, tooltip):
    pos = treeview.get_path_at_pos(x,y)
    if pos:
      path, col, x, y = pos
      if path:
        model = treeview.get_model()
        itr = model.get_iter(path)
        key = [model.get_value(itr,0)]
        if model.iter_parent(itr):
          key.insert(0,model.get_value(model.iter_parent(itr),0))
          if model.iter_parent(model.iter_parent(itr)):
            key.insert(0,model.get_value(model.iter_parent(model.iter_parent(itr)),0))
        key.insert(0,self.conn_id)
        key = tuple(key)
        note = get_metadata(key,'note')
        if not note: return False
        tooltip.set_text(note)
        return True


  def log_treeview_button_press_event(self, treeview, event):
    if event.button == 3:
      x = int(event.x)
      y = int(event.y)
      time = event.time
      pthinfo = treeview.get_path_at_pos(x, y)
      menu = gtk.Menu()

      item = gtk.MenuItem('Expand all')
      item.connect('activate', lambda x:(self.log_treeview.expand_all(),scroll_to_selection(self.log_treeview)) )
      menu.append(item)
      item = gtk.MenuItem('Collapse all')
      item.connect('activate', lambda x: self.log_treeview.collapse_all())
      menu.append(item)

      if pthinfo is not None:
        path, col, cellx, celly = pthinfo
        treeview.grab_focus()
        treeview.set_cursor( path, col, 0)
        model = treeview.get_model()
        itr = model.get_iter(path)
        if model.get_value(itr,0)=='SQL':
          print 'path', path
          item = gtk.CheckMenuItem('Hide This Statement')
          item.set_active(model.get_value(itr,2))
          sql = model.get_value(itr,1)
          sql_id = model.get_value(itr,3)
          def f(x, model, itr):
            sql_id = model.get_value(itr,3)
            log_conn = get_log_conn()
            cur = log_conn.cursor()
            cur.execute('update sql_log set hidden=:hidden where sql_id=:sql_id',
                        {'hidden':not model.get_value(itr,2), 'sql_id':sql_id})
            print 'saved', cur.rowcount, 'rows'
            log_conn.commit()
            cur.close()
            print sql_id, 'set to', model.get_value(itr,2)
            model.get_model().set_value( model.convert_iter_to_child_iter(itr), 4, 
                                         hidden_color(not model.get_value(itr,2)) )
            model.get_model().set_value( model.convert_iter_to_child_iter(itr), 2, not model.get_value(itr,2) )
            self.log_treefilter.refilter()
          item.connect('activate', f, model, itr)
          menu.append(item)
          rows = self.sql_log_cache_ids.get(sql_id)
          item = gtk.MenuItem('Load Cached Results' + (' (%i)'%rows if rows is not None else ''))
          item.set_sensitive(rows is not None)
          def load_cached_results(x, treeview, sql_id):
            self.results.clear_unsaved_tabs()
            log_conn = get_log_conn()
            log_cur = log_conn.cursor()
            log_cur.execute('select row_count, headers, data from sql_log_cache where sql_id=:sql_id',
                            {'sql_id':sql_id})
            for row in log_cur:
              row_count, headers, data = row[0], json.loads(row[1]), json.loads(row[2])
              page, treeview = self.results.create_query_results_page(sql,headers)
              load_data_to_treeview(headers, data, treeview)
              label_markup = '%i rows\n<i>(in %is)</i>'%(row_count,0)
              self.results.add_page_to_execute_notebook(page, label_markup=label_markup)
              break
            log_cur.close()
          item.connect('activate', load_cached_results, treeview, sql_id )
          menu.append(item)
        if model.get_value(itr,0)=='SESSION':
          item = gtk.MenuItem('Rename Session...')
          def f(x,session_id,model,itr):
            print 'session_id', session_id, x
            new_name = prompt_for_text('\nWhat would you like to rename this session to?', default=model.get_value(itr,1))
            if new_name:
              model.get_model().set_value(model.convert_iter_to_child_iter(itr),1,new_name)
              set_metadata(session_id,'name',new_name)
          item.connect('activate', f, model.get_value(itr,3), model, itr)
          menu.append(item)
          item = gtk.MenuItem('Resume Session')
          item.set_sensitive(False)
          menu.append(item)
        if model.get_value(itr,0)=='TRANSACTION':
          item = gtk.MenuItem('Rename Transaction...')
          def f(x,tran_id,model,itr):
            print 'tran_id', tran_id, x
            new_name = prompt_for_text('\nWhat would you like to rename this transaction to?', default=model.get_value(itr,1))
            if new_name:
              model.get_model().set_value(model.convert_iter_to_child_iter(itr),1,new_name)
              set_metadata(tran_id,'name',new_name)
          item.connect('activate', f, model.get_value(itr,3), model, itr)
          menu.append(item)

      item = gtk.MenuItem('Copy')
      def f(x,model,itr):
        s = self.log_treeview_row_repr(model,itr)
        get_clipboard().set_text(s)
        print s
      item.connect('activate', f, model, itr)
      menu.append(item)

      item = gtk.CheckMenuItem('Show Hidden Statements')
      item.set_active(self.log_show_hidden_statements)
      def f(x):
        self.log_show_hidden_statements = not self.log_show_hidden_statements
        self.log_treefilter.refilter()
      item.connect('activate', f)
      menu.append(item)
      menu.show_all()
      menu.popup( None, None, None, event.button, event.time)
      return True


  def metadata_treeview_button_press_event(self, treeview, event):
    if event.button == 3:
      x = int(event.x)
      y = int(event.y)
      time = event.time
      pthinfo = treeview.get_path_at_pos(x, y)
      menu = gtk.Menu()

      item = gtk.MenuItem('Expand all')
      item.connect('activate', lambda *_: (treeview.expand_all(),scroll_to_selection(treeview)))
      menu.append(item)
      item = gtk.MenuItem('Collapse all')
      item.connect('activate', lambda *_: treeview.collapse_all())
      menu.append(item)

      if pthinfo is not None:
        path, col, cellx, celly = pthinfo
        treeview.grab_focus()
        treeview.set_cursor( path, col, 0)
        model = treeview.get_model()
        itr = model.get_iter(path)
        if model.get_value(itr,1)=='table':
          table_name = model.get_value(itr,0)
          schema_name = model.get_value(model.iter_parent(itr),0)
          item = gtk.MenuItem('Select from table (TOP 1000)')
          def x(a,schema_name,table_name):
            self.set_query_to_select(schema_name,table_name)
          item.connect('activate', x, schema_name, table_name)
          menu.append(item)
          item = gtk.MenuItem('Add table to FROM')
          def x(a,schema_name,table_name):
            self.add_table_to_query(schema_name,table_name, force=True)
          item.connect('activate', x, schema_name, table_name)
          menu.append(item)
        if model.get_value(itr,1).startswith('column'):
          column_name = model.get_value(itr,0)
          table_name = model.get_value(model.iter_parent(itr),0)
          schema_name = model.get_value(model.iter_parent(model.iter_parent(itr)),0)
          item = gtk.MenuItem('Add column to SELECT')
          def x(a,schema_name,table_name,column_name):
            if self.add_table_to_query(schema_name,table_name):
              print 'added %s%s%s'%(schema_name,self.schema_tbl_sep,table_name)
              tbl_shortname = table_name
              if self.add_column_to_query(column_name,tbl_shortname):
                print 'added %s.%s'%(tbl_shortname,column_name)
          item.connect('activate', x, schema_name, table_name, column_name)
          menu.append(item)
          item = gtk.MenuItem('Add column to WHERE')
          def x(a,schema_name,table_name,column_name):
            if self.add_table_to_query(schema_name,table_name):
              print 'added %s%s%s'%(schema_name,self.schema_tbl_sep,table_name)
              tbl_shortname = table_name
              if self.add_column_to_where(column_name,tbl_shortname):
                print 'added %s.%s'%(tbl_shortname,column_name)
          item.connect('activate', x, schema_name, table_name, column_name)
          menu.append(item)
          item = gtk.MenuItem('Add column to ORDER BY')
          def x(a,schema_name,table_name,column_name):
            if self.add_table_to_query(schema_name,table_name):
              print 'added %s%s%s'%(schema_name,self.schema_tbl_sep,table_name)
              tbl_shortname = table_name
              if self.add_column_to_order_by(column_name,tbl_shortname):
                print 'added %s.%s'%(tbl_shortname,column_name)
          item.connect('activate', x, schema_name, table_name, column_name)
          menu.append(item)
        item = gtk.MenuItem('Add/edit note...')
        def add_note(x):
          key = [model.get_value(itr,0)]
          if model.iter_parent(itr):
            key.insert(0,model.get_value(model.iter_parent(itr),0))
            if model.iter_parent(model.iter_parent(itr)):
              key.insert(0,model.get_value(model.iter_parent(model.iter_parent(itr)),0))
          key.insert(0,self.conn_id)
          key = tuple(key)
          print key
          note = prompt_for_text('\nWhat would you like your note to be?', 
                                 default=get_metadata(key,'note',''), multiline=True)
          set_metadata(key,'note',note)
        item.connect('activate', add_note)
        menu.append(item)

      item = gtk.MenuItem('Reload metadata...')
      item.connect('activate', lambda *_: prompt_yes_no('''Would you like to reload the schema, table, '''+
          '''and column metadata from the database?''') and self.load_metadata(use_cache=False))
      menu.append(item)

      menu.show_all()
      menu.popup( None, None, None, event.button, event.time)
      return True


  def log_treefilter_callback(self, model, itr):
    if not self.log_show_hidden_statements and model.get_value(itr,2):
      return False
    if model.iter_n_children(itr):
      for i in range(model.iter_n_children(itr)):
        if self.log_treefilter_callback(model,model.iter_nth_child(itr,i)):
          return True
      if not self.log_show_hidden_statements: return False
    v = str(model.get_value(itr,0))+' '+str(model.get_value(itr,1))
    s = self.log_entry.get_text()
    return v.lower().find(s.lower())>=0


  def metadata_treefilter_callback(self, model, itr):
    if model.iter_n_children(itr):
      for i in range(model.iter_n_children(itr)):
        if self.metadata_treefilter_callback(model,model.iter_nth_child(itr,i)):
          return True
    v = str(model.get_value(itr,0))+' '+str(model.get_value(itr,1))
    if not v: return True
    s = self.metadata_entry.get_text()
    parent = model.iter_parent(itr)
    if parent:
      v += ' '+ str(model.get_value(parent,0))+' '+str(model.get_value(parent,1))
    return v.lower().find(s.lower())>=0


  def start_session(self):
    self.session_id = str(uuid.uuid4())
    now = time.time()
    d = time.strftime("%A, %B %d %Y %I:%M%p",time.localtime(now))
    self.rollback_button.set_sensitive(False)
    self.commit_button.set_sensitive(False)
    self.session_row = self.log_treestore.append(None, ['SESSION','Session for %s starting on '%get_conn_info(self.conn_id)['server']+d,False,self.session_id,hidden_color(False),now,pango.STYLE_OBLIQUE])


  def start_trans(self):
    self.tran_id = str(uuid.uuid4())
    self.tran_start = now = time.time()
    d = time.strftime("%A, %B %d %Y %I:%M%p",time.localtime(now))
    self.rollback_button.set_sensitive(False)
    self.commit_button.set_sensitive(False)
    self.tran_row = self.log_treestore.append(self.session_row, ['TRANSACTION','Transaction for %s starting on '%get_conn_info(self.conn_id)['server']+d,False,self.tran_id,hidden_color(False),now,pango.STYLE_OBLIQUE])
    label = self.notebook.get_tab_label(self.node())
    with gtk.gdk.lock:
      while len(label.get_children())>1:
        label.remove(label.get_children()[-1])
      img = gtk.image_new_from_stock(gtk.STOCK_MEDIA_RECORD, gtk.ICON_SIZE_MENU)
      img.set_tooltip_text('A transaction is currently active')
      img.show()
      label.add(img)


  def rollback(self):
    start_time = time.time()
    with self.conn_lock:
      conn = self.get_conn()
      if self.db_type=='SQL Server':
        cur = conn.cursor()
        cur.execute('rollback')
        cur.close()
      else:
        conn.rollback()
    exec_time = time.time() - start_time
    sql_id = str(uuid.uuid4())
    self.log_treestore.append(self.tran_row, ['SQL','rollback;',False, sql_id,hidden_color(False),start_time,pango.STYLE_NORMAL])
    log_conn = get_log_conn()
    log_cur = log_conn.cursor()
    params = {'conn_id':self.conn_id, 'session_id':self.session_id, 'start_time':start_time, 'tran_id':self.tran_id, 
              'sql_id':sql_id, 'sql':'rollback;', 'exec_time':exec_time, 'hidden':False,}
    log_cur.execute('insert into sql_log (conn_id, session_id, tran_id, sql_id, sql, start_time, exec_time, hidden) '+
                    'values (:conn_id, :session_id, :tran_id, :sql_id, :sql, :start_time, :exec_time, :hidden)', params)
    log_cur.close()
    log_conn.commit()
    self.reset_trans_stuff()


  def reset_trans_stuff(self):
    self.tran_id = None
    self.rollback_button.set_sensitive(False)
    self.commit_button.set_sensitive(False)
    label = self.notebook.get_tab_label(self.node())
    while len(label.get_children())>1:
      label.remove(label.get_children()[-1])
    img = gtk.image_new_from_stock(gtk.STOCK_CONNECT, gtk.ICON_SIZE_MENU)
    img.set_tooltip_text('Connection to database is active')
    img.show()
    label.add(img)
    
    
  def commit(self):
    start_time = time.time()
    with self.conn_lock:
      if self.db_type=='SQL Server':
        cur = self.get_conn().cursor()
        cur.execute('commit')
        cur.close()
      else:
        self.get_conn().commit()
    exec_time = time.time() - start_time
    sql_id = str(uuid.uuid4())
    self.log_treestore.append(self.tran_row, ['SQL','commit;',False,sql_id,hidden_color(False),start_time,pango.STYLE_NORMAL])
    log_conn = get_log_conn()
    log_cur = log_conn.cursor()
    params = {'conn_id':self.conn_id, 'session_id':self.session_id, 'start_time':start_time, 'tran_id':self.tran_id, 
              'sql_id':sql_id, 'sql':'commit;', 'exec_time':exec_time, 'hidden':False, }
    log_cur.execute('insert into sql_log (conn_id, session_id, tran_id, sql_id, sql, start_time, exec_time, hidden) '+
                    'values (:conn_id, :session_id, :tran_id, :sql_id, :sql, :start_time, :exec_time, :hidden)', params)
    log_cur.close()
    log_conn.commit()
    self.reset_trans_stuff()
    
    
  def log_treeview_row_repr(self, model, itr):
    if model.get_value(itr,0)=='SESSION':
      parent = itr
      n = model.iter_n_children(parent)
      sql = []
      sql.append( '-- '+model.get_value(parent,1) )
      for i in range(n):
        itr2 = model.iter_nth_child(parent, i)
        if model.get_value(itr2,0)=='TRANSACTION':
          sql.append( '-- '+model.get_value(itr2,1) )
          m = model.iter_n_children(itr2)
          for i in range(m):
            itr3 = model.iter_nth_child(itr2, i)
            sql.append( model.get_value(itr3,1) )
        else:
          sql.append( model.get_value(itr2,1) )
      return '\n'.join(sql)
        
    if model.get_value(itr,0)=='TRANSACTION':
      parent = itr
      n = model.iter_n_children(parent)
      sql = []
      sql.append( '-- '+model.get_value(parent,1) )
      for i in range(n):
        sql.append( model.get_value(model.iter_nth_child(parent, i),1) )
      return'\n'.join(sql)
        
    if model.get_value(itr,0)=='SQL':
      sql = model.get_value(itr,1)
      return sql

  def log_treeview_row_activated(self, treeview, path, view_column):
    model = treeview.get_model()
    itr = model.get_iter(path)
    s = self.log_treeview_row_repr(model,itr)
    self.query.get_buffer().set_text(s)


  def metadata_treeview_row_activated(self, treeview, path, view_column):
    model = treeview.get_model()
    itr = model.get_iter(path)
    if model.get_value(itr,1)=='table':
      table_name = model.get_value(itr,0)
      schema_name = model.get_value(model.iter_parent(itr),0)
      self.add_table_to_query(schema_name,table_name, force=True)
      print 'added %s%s%s'%(schema_name,self.schema_tbl_sep,table_name)
    if model.get_value(itr,1).startswith('column'):
      column_name = model.get_value(itr,0)
      table_name = model.get_value(model.iter_parent(itr),0)
      schema_name = model.get_value(model.iter_parent(model.iter_parent(itr)),0)
      if self.add_table_to_query(schema_name,table_name):
        print 'added %s%s%s'%(schema_name,self.schema_tbl_sep,table_name)
        tbl_shortname = table_name
        if self.add_column_to_query(column_name,tbl_shortname):
          print 'added %s.%s'%(tbl_shortname,column_name)
      else:
        buf = self.query.get_buffer()
        sql = buf.get_text(buf.get_start_iter(),buf.get_end_iter())
        if sql: sql += '\n\n'
        sql += 'select %s.%s from %s%s%s;' % (table_name,column_name,schema_name,self.schema_tbl_sep,table_name)
        buf.set_text(sql)
    if model.get_value(itr,1).startswith('relationship'):
      s = model.get_value(itr,0)
      r_schema_name, r_table_column = s.split(self.schema_tbl_sep,1)
      r_table_name, r_column_name = r_table_column.split('.',1)
      column_name = model.get_value(model.iter_parent(itr),0)
      table_name = model.get_value(model.iter_parent(model.iter_parent(itr)),0)
      schema_name = model.get_value(model.iter_parent(model.iter_parent(model.iter_parent(itr))),0)
      print (schema_name, table_name, column_name), '=>', (r_schema_name, r_table_name, r_column_name)
      self.add_table_to_query(schema_name, table_name, force=True)
      self.add_table_to_query(r_schema_name, r_table_name, force=True)
      
  
  def add_table_to_query(self,schema_name,table_name,force=False):
    buf = self.query.get_buffer()
    sql = buf.get_text(buf.get_start_iter(),buf.get_end_iter())
    sql = sql.strip()
    parse = sqlparse.parse(sql)
    parse = [stmt for stmt in parse if str(stmt).strip()]
    if parse:
      stmt = parse[-1]
      for token in stmt.tokens:
        print token.__class__, token, token.value, token.ttype
      from_token = stmt.token_next_match(0, sqlparse.tokens.Token.Keyword, 'from')
      print 'from_token', from_token
      if from_token:
        next_token = stmt.token_next(from_token)
        print 'next_token', next_token.__class__, next_token, next_token.value, next_token.ttype
        for token in next_token.tokens:
          print token.__class__, token, token.value, token.ttype
        if isinstance(next_token,sqlparse.sql.Identifier):
          stmt.tokens[stmt.token_index(next_token)] = next_token = sqlparse.sql.IdentifierList(tokens=[next_token])
        assert isinstance(next_token,sqlparse.sql.IdentifierList)
        for token in next_token.tokens:
          print token.__class__, token, token.value, token.ttype
          if isinstance(token,sqlparse.sql.Identifier) and token.tokens[-1].value==table_name:
            print 'table already in query'
            return True
        next_token.tokens.append( sqlparse.sql.Token(sqlparse.tokens.Token.Punctuation,',') )
        next_token.tokens.append( sqlparse.sql.Token(sqlparse.tokens.Token.Text.Whitespace,' ') )
        next_token.tokens.append( sqlparse.sql.Identifier(tokens=[
            sqlparse.sql.Token(sqlparse.tokens.Token.Name,schema_name),
            sqlparse.sql.Token(sqlparse.tokens.Token.Punctuation,self.schema_tbl_sep),
            sqlparse.sql.Token(sqlparse.tokens.Token.Name,table_name),
        ]) )
        print 'next_token', next_token
        print stmt
        buf.set_text( ''.join([str(x) for x in parse]) )
        self.check_for_missing_fk_constraints()
        return True
    if force:
      set_query_to_select(self,schema_name,table_name)
      
      
  def set_query_to_select(self,schema_name,table_name,max_count=5000):
    buf = self.query.get_buffer()
    sql = buf.get_text(buf.get_start_iter(),buf.get_end_iter())
    if sql: sql += '\n\n'
    top_line = 'top %i '%max_count if self.db_type=='SQL Server' else ''
    limit_line = ' limit %i'%max_count if self.db_type in ('MySQL','PostgreSQL') else ''
    sql += 'select %s* from %s%s%s%s;' % (top_line,schema_name,self.schema_tbl_sep,table_name,limit_line)
    buf.set_text(sql)
      
    
  def add_column_to_query(self,column_name,table_name):
    buf = self.query.get_buffer()
    sql = buf.get_text(buf.get_start_iter(),buf.get_end_iter())
    parse = sqlparse.parse(sql)
    if parse:
      stmt = parse[-1]
      for token in stmt.tokens:
        print token.__class__, token, token.value, token.ttype
      select_token = stmt.token_next_match(0, sqlparse.tokens.Token.Keyword, 'select')
      from_token = stmt.token_next_match(0, sqlparse.tokens.Token.Keyword, 'from')
      print 'stmt', stmt.__class__, stmt, stmt.value, stmt.ttype
      for token in stmt.tokens:
        print token.__class__, token, token.value, token.ttype
      print 'select_token', select_token
      print 'from_token', from_token
      if from_token:
        i = stmt.token_index(from_token) - 1
        print i
        stmt.tokens.insert(i,sqlparse.sql.Token(sqlparse.tokens.Token.Name,column_name))
        stmt.tokens.insert(i,sqlparse.sql.Token(sqlparse.tokens.Token.Punctuation,'.'))
        stmt.tokens.insert(i,sqlparse.sql.Token(sqlparse.tokens.Token.Name,table_name))
        stmt.tokens.insert(i,sqlparse.sql.Token(sqlparse.tokens.Token.Whitespace,' '))
        stmt.tokens.insert(i,sqlparse.sql.Token(sqlparse.tokens.Token.Punctuation,','))
        print stmt
        buf.set_text( ''.join([str(x) for x in parse]) )
        return True


  def add_column_to_where(self,column_name,table_name):
    buf = self.query.get_buffer()
    sql = buf.get_text(buf.get_start_iter(),buf.get_end_iter())
    parse = sqlparse.parse(sql)
    if parse:
      stmt = parse[-1]
      
      if not [token for token in stmt.tokens if isinstance(token,sqlparse.sql.Where)]:
        from_token = stmt.token_next_match(0, sqlparse.tokens.Token.Keyword, 'from')
        i = stmt.token_index(from_token)+3 # whitespace + from + sqlparse.sql.Identifier|sqlparse.sql.IdentifierList
        stmt.tokens.insert(i,' ')
        stmt.tokens.insert(i+1,sqlparse.sql.Where(tokens=['where']))
      
      for token in stmt.tokens:
        if isinstance(token,sqlparse.sql.Where):
          print 'where token.tokens', token.tokens
          if len(token.tokens)>1:
            token.tokens.append(' ')
            token.tokens.append('and')
          token.tokens.append(' ')
          token.tokens.append(table_name+'.'+column_name+"=?")
      
      print stmt
      buf.set_text( ''.join([str(x) for x in parse]) )
      return True


  def add_column_to_order_by(self,column_name,table_name):
    buf = self.query.get_buffer()
    sql = buf.get_text(buf.get_start_iter(),buf.get_end_iter())
    parse = sqlparse.parse(sql)
    if parse:
      stmt = parse[-1]
      
      last_token = None
      if stmt.tokens[-1].ttype==sqlparse.tokens.Token.Punctuation:
        last_token = stmt.tokens.pop()
      if stmt.token_next_match(0, sqlparse.tokens.Token.Keyword, 'order'):
        stmt.tokens.append(',')
      else:
        stmt.tokens.append(' ')
        stmt.tokens.append('order')
        stmt.tokens.append(' ')
        stmt.tokens.append('by')
      stmt.tokens.append(' ')
      stmt.tokens.append(table_name+'.'+column_name)
      if last_token:
        stmt.tokens.append(last_token)
      
      print stmt
      buf.set_text( ''.join([str(x) for x in parse]) )
      return True


  def get_tables_from_stmt(self,stmt):
    seen_tables = set()
    from_token = stmt.token_next_match(0, sqlparse.tokens.Token.Keyword, 'from')
    tables = stmt.token_next(from_token)
    if isinstance(tables,sqlparse.sql.IdentifierList):
      for table in tables.tokens:
        print 'table', table, table.__class__
        if isinstance(table,sqlparse.sql.Identifier):
          if len(table.tokens)==3:
            seen_tables.add( (str(table.tokens[0]),str(table.tokens[2])) )
          else:
            seen_tables.add( (self.schema,str(table.tokens[0])) )
    return seen_tables
    
  
  def check_for_missing_fk_constraints(self):
    buf = self.query.get_buffer()
    sql = buf.get_text(buf.get_start_iter(),buf.get_end_iter())
    parse = sqlparse.parse(sql)
    if parse:
      stmt = parse[-1]
      seen_tables = list(self.get_tables_from_stmt(stmt))
      where_token = None
      from_token = stmt.token_next_match(0, sqlparse.tokens.Token.Keyword, 'from')
      #print 'stmt', stmt.tokens
      for t in stmt.get_sublists():
        #print 't', t, t.ttype, t.tokens
        if t.tokens and str(t.tokens[0]).lower()=='where':
          where_token = t
          break
      if where_token==None:
        #print 'not where_token'
        where_token = sqlparse.sql.TokenList(tokens=[ sqlparse.sql.Token('unk',' where'),sqlparse.sql.Token('unk',' ') ])
        stmt.tokens.insert(stmt.token_index(from_token)+3,where_token)
      #print 'where_token.tokens', where_token.tokens
      
      # get set of comparisons already there
      existing_comparisons = set()
      for t in where_token.tokens:
        if isinstance(t,sqlparse.sql.Comparsion):
          identifiers = [ x for x in t.tokens if isinstance(x,sqlparse.sql.Identifier) ]
          if len(identifiers)==2:
            id1 = tuple([ str(x) for x in identifiers[0].tokens if isinstance(x,sqlparse.sql.Token) and str(x)!='.' ])
            #print 'id1', id1
            id2 = tuple([ str(x) for x in identifiers[1].tokens if isinstance(x,sqlparse.sql.Token) and str(x)!='.' ])
            #print 'id2', id2
            existing_comparisons.add((id1,id2))
            existing_comparisons.add((id2,id1))
#          try: print 't.tokens', t.tokens
#          except: print 't', t
      print 'existing_comparisons', existing_comparisons
        
      print 'seen_tables', seen_tables
      handled_tables = set()
      for schema,table in seen_tables:
        if (schema,table) in handled_tables: continue
        for column,info in self.metadata[schema][table].iteritems():
          #print 'relA', schema,table,column
          fk = self.relationships.get( (self.conn_id,schema,table,column))
          if fk:
            r_conn_id,r_schema,r_table,r_column = fk
            #print 'relB', r_schema,r_table,r_column
            if not (r_schema,r_table) in seen_tables: continue
            if ((table,column),(r_table,r_column)) in existing_comparisons: continue
            #print 'relationship', r_schema,r_table,r_column
            comparsion = sqlparse.sql.Comparsion(tokens=[
              sqlparse.sql.Token('unk',table+'.'+column),
              sqlparse.sql.Token('unk',' = '),
              sqlparse.sql.Token('unk',r_table+'.'+r_column),
            ])
            #if len(where_token.tokens)>2:
              #print 'where_token.tokens[2]', where_token.tokens[2], where_token.tokens[2].__class__
            if len(where_token.tokens)>2 and isinstance(where_token.tokens[2],sqlparse.sql.Comparsion):
              where_token.tokens.insert(2,sqlparse.sql.Token('unk',' and '))
            where_token.tokens.insert(2,comparsion)
            handled_tables.add((r_schema,r_table))
          
      #print 'where', where_token
      if handled_tables:
        #print stmt
        buf.set_text( ''.join([str(x) for x in parse]) )
      
  
  

  def format(self):
    print 'formatting'
    buf = self.query.get_buffer()
    sql = buf.get_text(buf.get_start_iter(),buf.get_end_iter())
    sql = sqlparse.format(sql,reindent=True)
    buf.set_text(sql)


  def load_metadata(self, use_cache=True):
  
    def run():

      if use_cache:
        self.metadata = get_metadata(self.conn_id,'metadata')
      else:
        self.metadata = None
      
      if not self.metadata:
        print 'loading metadata for', self.conn_id, 'from db...'
        self.metadata = metadata.extract_metadata(self.conn_lock, self.get_conn(), self.db_type)
        
      # cache metadata
      set_metadata(self.conn_id,'metadata',self.metadata)
          
      
      self.relationships = {} # (conn_id,schema,table,column) => (conn_id,schema,table,column)
      
      if self.db_type=='MySQL':
        cur = self.get_conn().cursor()
        cur.execute('''SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME,
                         REFERENCED_COLUMN_NAME
                       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                       WHERE TABLE_SCHEMA is not null and TABLE_NAME is not null
                         and COLUMN_NAME is not null and REFERENCED_TABLE_SCHEMA is not null
                         and REFERENCED_TABLE_NAME is not null and REFERENCED_COLUMN_NAME is not null''')
        for row in cur:
          self.relationships[(self.conn_id,)+tuple(row[:3])] = (self.conn_id,)+tuple(row[3:])
        cur.close()
        
      # load false keys
      log_conn = get_log_conn()
      cur = log_conn.cursor()
      cur.execute('select conn_id, schema_name, table_name, column_name, from_conn_id, from_schema_name, from_table_name, from_column_name from false_keys')
      for row in cur:
        self.relationships[tuple(row[4:])] = tuple(row[:4])

      print 'populating metadata treeview...'
      with gtk.gdk.lock:
        self.metadata_treestore.clear()
        for schema, tables in sorted(self.metadata.iteritems()):
          a = self.metadata_treestore.append(None, [schema,'schema',''])
          for table, columns in sorted(tables.iteritems()):
            b = self.metadata_treestore.append(a, [table,'table',''])
            for column, info in sorted(columns.iteritems()):
              fk = self.relationships.get((self.conn_id,schema,table,column))
              c = self.metadata_treestore.append(b, [
                  column,
                  u'column (%s)'%(info['datatype']),
                  PK_KEY if info.get('primary_key') else ''
              ])
              if fk:
                #print (schema, table, column),fk
                r_conn_id,r_schema,r_table,r_column = fk
                self.metadata_treestore.append(c, [r_schema+self.schema_tbl_sep+r_table+'.'+r_column,'foreign key',''])
      print '...done'

    self.metadata_treestore.clear()
    self.metadata_treestore.append(None, ['loading metadata...','',''])
    t = threading.Thread(target=run)
    t.start()
          

  def load_sql_log(self):
    log_conn = get_log_conn()
    cur = log_conn.cursor()
    cur.execute('select conn_id, session_id, tran_id, sql_id, sql, start_time, exec_time, hidden from sql_log where '+
                'conn_id=? order by start_time', (self.conn_id,))
    tran_id_row_map = {}
    session_id_row_map = {}
    for row in cur:
      conn_id, session_id, tran_id, sql_id, sql, start_time, exec_time, hidden = tuple(row)
      if session_id not in session_id_row_map:
        #print 'found session', session_id
        name = get_metadata(session_id,'name',default='Session starting on '+datetime.datetime.fromtimestamp(start_time).strftime("%A, %B %d %Y %I:%M%p"))
        session_row = self.log_treestore.append(None, ['SESSION',name,False,session_id,hidden_color(False),start_time,pango.STYLE_OBLIQUE])
        session_id_row_map[session_id] = session_row
      if tran_id:
        if tran_id not in tran_id_row_map:
          #print 'found tran', tran_id
          name = get_metadata(tran_id,'name','Transaction starting on '+datetime.datetime.fromtimestamp(start_time).strftime("%A, %B %d %Y %I:%M%p"))
          tran_row = self.log_treestore.append(session_row, ['TRANSACTION',name,False,tran_id,hidden_color(False),start_time,pango.STYLE_OBLIQUE])
          tran_id_row_map[tran_id] = tran_row
        tran_row = tran_id_row_map[tran_id]
        self.log_treestore.append(tran_row, ['SQL',sql,hidden,sql_id,hidden_color(hidden),start_time,pango.STYLE_NORMAL])
      else:
        self.log_treestore.append(session_row, ['SQL',sql,hidden,sql_id,hidden_color(hidden),start_time,pango.STYLE_NORMAL])
    for tran_id,row in tran_id_row_map.iteritems():
      last_child = self.log_treestore.iter_nth_child(row,self.log_treestore.iter_n_children(row)-1)
      if self.log_treestore.get_value(last_child,1).strip().strip(';').lower() not in ('rollback','commit'):
        self.log_treestore.append(row, ['SQL','-- implicit rollback;',False,None,hidden_color(False),self.log_treestore.get_value(last_child,5),pango.STYLE_NORMAL])

    self.sql_log_cache_ids = {}
    cur.execute('select sql_id, row_count from sql_log_cache')
    for row in cur:
      self.sql_log_cache_ids[row[0]] = row[1]


  def execute_entry(self):
    self.results.clear_unsaved_tabs()
    tmp_page = gtk.Alignment(xalign=0.5, yalign=0.5, xscale=0.0, yscale=0.0)
    tmp_page.add(gtk.Label('Please wait: Your SQL statement is executing...'))
    self.results.execute_notebook.append_page(tmp_page, tab_label=gtk.Label('executing...'))
    tmp_page.show_all()
    buf = self.query.get_buffer()
    if buf.get_has_selection():
      start_iter, end_iter = buf.get_selection_bounds()
      sql = buf.get_text(start_iter,end_iter).strip()
    else:
      sql = buf.get_text(buf.get_start_iter(),buf.get_end_iter()).strip()
    parse = sqlparse.parse(sql)
    print 'thread.get_ident()', thread.get_ident()
    def run():
      for stmt in parse:
        self.execute(str(stmt).strip())
      with gtk.gdk.lock:
        self.results.execute_notebook.remove_page(self.results.execute_notebook.page_num(tmp_page))
        self.results.update_notebook_visible()
    t = threading.Thread(target=run)
    t.start()

    
  def execute(self, sql, treeview=None, log=True):

    with self.conn_lock:
    
      conn = self.get_conn()
      start_time = time.time()
      cur = conn.cursor()
      try:
  
        print 'executing:', repr(sql)
        result = cur.execute(sql)
        exec_time = time.time()-start_time
        print 'exec_time', exec_time
        print 'result', result
        print 'cur.description', cur.description
        print 'cur.rowcount', cur.rowcount

        if log:
          # log sql
          sql_id = str(uuid.uuid4())
          if not cur.description and not self.tran_id:
            self.start_trans()
          with gtk.gdk.lock:
            if self.tran_id:
              itr = self.log_treestore.append(self.tran_row, ['SQL',sql,False,sql_id,hidden_color(False),start_time,pango.STYLE_NORMAL])
            else:
              itr = self.log_treestore.append(self.session_row, ['SQL',sql,False,sql_id,hidden_color(False),start_time,pango.STYLE_NORMAL])
          log_conn = get_log_conn()
          log_cur = log_conn.cursor()
          params = {'conn_id':self.conn_id, 'session_id':self.session_id, 'start_time':start_time, 
                    'tran_id':self.tran_id, 'sql_id':sql_id, 'sql':sql, 'exec_time':exec_time, 
                    'hidden':False}
          log_cur.execute('insert into sql_log (conn_id, session_id, tran_id, sql_id, sql, '+
                          'start_time, exec_time, hidden) values (:conn_id, :session_id, :tran_id, '+
                          ':sql_id, :sql, :start_time, :exec_time, :hidden)', params)
          log_cur.close()
          log_conn.commit()
  
        if cur.description: # this was a query
          row_count, headers, data = get_count_headers_data_from_cur(cur)
          
          fh, fn = tempfile.mkstemp(prefix='desql_')
          with os.fdopen(fh,'wb') as f:
            f.write(','.join(headers))
            f.write('\n')
            for row in data:
              f.write(','.join(row))
              f.write('\n')
          
          with gtk.gdk.lock:
            if treeview:
              load_data_to_treeview(headers, data, treeview)
            else:
              page, treeview = self.results.create_query_results_page(sql,headers)
              load_data_to_treeview(headers, data, treeview)
              label_markup = '%i rows\n<i>(in %is)</i>'%(row_count,exec_time)
              self.results.add_page_to_execute_notebook(page, label_markup=label_markup)
          if log:
            log_conn = get_log_conn()
            log_cur = log_conn.cursor()
            params = {'sql_id':sql_id, 'row_count':row_count, 'headers':json.dumps(headers), 'data':json.dumps(data) }
            log_cur.execute('insert into sql_log_cache (sql_id, row_count, headers, data) values (:sql_id, :row_count, '+
                            ':headers, :data)', params)
            log_cur.close()
            log_conn.commit()
            self.sql_log_cache_ids[sql_id] = row_count
        else:
          with gtk.gdk.lock:
            self.rollback_button.set_sensitive(True)
            self.commit_button.set_sensitive(True)
            page = gtk.Label('%i row%s updated'%(cur.rowcount,pluralize(cur.rowcount)))
            label_markup = 'response\n<i>(in %is)</i>'%(exec_time,)
            self.results.add_page_to_execute_notebook(page, label_markup=label_markup)
      
        if log:
          with gtk.gdk.lock:
            try:
              filter_iter = self.log_treefilter.convert_child_iter_to_iter(itr)
              filter_path = self.log_treefilter.get_path(filter_iter)
              self.log_treeview.expand_to_path(filter_path)
              self.log_treeview.scroll_to_cell(filter_path)
            except:
              # scroll to latest entry doesn't work if filtering is applied
              pass
        
      except Exception as e:
        traceback.print_exc()
        with gtk.gdk.lock:
          page = gtk.TextView()
          page.set_wrap_mode(gtk.WRAP_WORD)
          page.set_editable(False)
          try:
            page.get_buffer().set_text('%s\n(error:%i)'%(e[1],e[0]))
          except:
            page.get_buffer().set_text(str(e))
          self.results.add_page_to_execute_notebook(page, label_markup='<i>error</i>')
  
      cur.close()
  
    
  def close(self):
    #self.conn.rollback()
    if self._conn is not None:
      self.get_conn().close()
    self.notebook.remove_page( self.notebook.get_current_page() )
    self.main.conns.remove(self)
    self.main.no_conns_bar.set_visible(len(self.main.conns)==0)
    self.main.notebook.set_visible(len(self.main.conns)!=0)

    
  def node(self):
    return self.top
    
    


